lyfz_repos/铂金版源码/DBFile/SQL/2015-08-13[收集]/2015-10-03(腾工).txt

if not exists
(select * from syscolumns where id=object_id('tb_ErpOrdersPhotography') and name='Ordpg_SightsRemark')
begin
	alter table tb_ErpOrdersPhotography add Ordpg_SightsRemark  nvarchar(800) NULL
end
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'tb_ErpOrdersPhotography', N'COLUMN',N'Ordpg_SightsRemark'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'景点备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpOrdersPhotography', @level2type=N'COLUMN',@level2name=N'Ordpg_SightsRemark'
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_DoorCityOpenOrderFriendSelect')
BEGIN
	DROP VIEW [dbo].BView_DoorCityOpenOrderFriendSelect
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_DoorCityOpenOrderFriendSelect]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_DoorCityOpenOrderFriendSelect]
AS
SELECT   
dbo.tb_ErpCustomer.ID, 
Cus_CustomerNumber, 
Cus_Type,
Cus_Name, 
Cus_NamePinyin,
Cus_Telephone, 
Cus_Sex, 
Mc_CradNumber
FROM dbo.tb_ErpCustomer LEFT OUTER JOIN
dbo.tb_ErpMemberCard ON 
Cus_CustomerNumber = Mc_CustomerNumber AND 
Mc_CradNumber <> ''''
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_MemberMain_GoldMember')
BEGIN
	DROP VIEW [dbo].BView_MemberMain_GoldMember
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_MemberMain_GoldMember]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_MemberMain_GoldMember]
AS
SELECT   Cus_ID, ID AS Mc_ID, 
Mc_Status, 
Mc_Number, 
Mc_CradNumber AS 会员卡号, 
Cus_Name AS 姓名, 
Cus_CustomerNumber AS 姓名编号,
dbo.fn_CheckSex(Cus_Sex) AS 性别, 
Cus_Telephone AS 电话, 
Mc_CardType AS 类型, 
dbo.fn_GetMember(Mc_Number, 1) AS 积分, 
Mc_Money AS 金额, 
dbo.fn_GetMember(Mc_Number, 2) AS 折扣, 
dbo.fn_GetMember(Mc_Number, 3) AS 套系金额, 
dbo.fn_GetMember(Mc_Number, 4) AS 已返金额, 
dbo.fn_GetMember(Mc_Number, 5) AS 已取出金额,
(SELECT COUNT(Mcse_ServiceCount) AS Expr1 FROM dbo.tb_ErpMemberCardServiceContent WHERE (Mcse_Number = dbo.BView_MemberCard.Mc_Number)) AS 服务次数,
(SELECT COUNT(Mvc_Number) AS Expr1 FROM dbo.tb_ErpMemberCardViceCard WHERE (Mvc_Number = dbo.BView_MemberCard.Mc_Number) AND (Mvc_Status = ''1'') AND (Mvc_ViceCardType = ''积分副卡'')) AS 积分副卡,
(SELECT COUNT(Mvc_Number) AS Expr1 FROM dbo.tb_ErpMemberCardViceCard AS tb_ErpMemberViceCard_2 WHERE (Mvc_Number = dbo.BView_MemberCard.Mc_Number) AND (Mvc_Status = ''1'') AND (Mvc_ViceCardType = ''现金副卡'')) AS 现金副卡,
(SELECT COUNT(Mvc_Number) AS Expr1 FROM dbo.tb_ErpMemberCardViceCard AS tb_ErpMemberViceCard_1 WHERE (Mvc_Number = dbo.BView_MemberCard.Mc_Number) AND (Mvc_Status = ''1'') AND (Mvc_ViceCardType = ''返现副卡'')) AS 返现副卡, 
Mc_Remark AS 备注, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Mc_CreateDatetime)) AS 办卡日期
FROM      dbo.BView_MemberCard
WHERE   (Mc_Status = ''1'')
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_DoorCityTotakePieceMonthReworkStatistic')
BEGIN
	DROP VIEW [dbo].BView_DoorCityTotakePieceMonthReworkStatistic
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_DoorCityTotakePieceMonthReworkStatistic]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_DoorCityTotakePieceMonthReworkStatistic]
AS
SELECT   
tb_ErpOrder.ID, dbo.tb_ErpOrder.Ord_DividedShop, dbo.tb_ErpOrder.Ord_Type, tb_ErpOrderProductList_1.ID AS PPID, 
dbo.tb_ErpOrder.Ord_Number, dbo.tb_ErpOrderDigital.Ordv_ViceNumber,
Ordv_DigitalNumber, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号,
Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
tb_ErpOrderProductList_1.OPlist_ProdNumber, tb_ErpOrderProductList_1.OPlist_ProdName AS 产品名称, 
tb_ErpOrderProductList_1.OPlist_Class,
(SELECT Sc_ClassName FROM dbo.tb_ErpSystemCategory WHERE (Sc_ClassCode = tb_ErpOrderProductList_1.OPlist_Class)) AS 商品类别, 
tb_ErpOrderProductList_1.OPlist_ProdQuantity AS 数量, tb_ErpOrderProductList_1.OPlist_SendVendor AS 是否发出, 
dbo.fn_CheckProductReworkStatus(tb_ErpOrderProductList_1.OPlist_ReworkStatus) 
+ CASE OPlist_ReworkRemark WHEN '''' THEN '''' ELSE ''/['' + OPlist_ReworkRemark + '']'' END AS 是否返工, 
OPlist_ReworkRemark2 AS 返工原因,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(tb_ErpOrderProductList_1.OPlist_ReworkTime)) AS 返工日期, 
tb_ErpOrderProductList_1.OPlist_ReworkTime AS 返工日期查询, 
dbo.fn_CheckProductCompletedStatus(tb_ErpOrderProductList_1.OPlist_CompletedStatus) AS 是否完成, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(tb_ErpOrderProductList_1.OPlist_CompletedTime)) AS 完成日期,
(SELECT   COUNT(ID) AS Expr1 FROM      dbo.tb_ErpOrderProductList
WHERE   (OPlist_ViceNumber = dbo.tb_ErpOrderDigital.Ordv_ViceNumber) AND (OPlist_Type = ''2'')) AS Ord_ViceOrderCount
FROM      dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number LEFT OUTER JOIN
dbo.tb_ErpOrderProductList AS tb_ErpOrderProductList_1 ON 
tb_ErpOrderProductList_1.OPlist_ViceNumber = dbo.tb_ErpOrderDigital.Ordv_ViceNumber
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'') AND (tb_ErpOrderProductList_1.OPlist_Type = ''2'')
' 
GO